package cs236369.hw5;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

public class InitListener implements ServletContextListener {
	
	private static BoneCPConfig connectionPoolConfig;		// configurations of the connections pool
	private static BoneCP connectionPool;					// the connections pool
	private static final String configFilePath = "/WEB-INF/config";
	
	// database connection constants
	private static String dbURL;
	private static String dbUserName;
	private static String dbPassword;
	
	public static void readDbData(ServletContext ctx) {
		BufferedReader br = null;
		Map<String, String> map = new HashMap<String, String>();
		
		try {
			String line;
			br = new BufferedReader(new InputStreamReader(ctx.getResourceAsStream(configFilePath)));
			String[] lineArray;
			while ((line = br.readLine()) != null) {
				lineArray = line.split("\t");
				map.put(lineArray[0], lineArray[1]);
			}
			dbURL = map.get("dburl");
			dbUserName = map.get("dbusrname");
			dbPassword = map.get("dbpassword");
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (br != null)br.close();
			} catch (IOException ex) {
				ex.printStackTrace();
			}
		}
	}
	
	@Override
	public void contextDestroyed(ServletContextEvent event) {
		connectionPool.shutdown();
	}
	
	@Override
	public void contextInitialized(ServletContextEvent event) {
		try {
			readDbData(event.getServletContext());
			//Add driver
			Class.forName("org.postgresql.Driver");
		 			
			// create a new connections pool configuration
			connectionPoolConfig = new BoneCPConfig();
			connectionPoolConfig.setJdbcUrl(dbURL);
			connectionPoolConfig.setUsername(dbUserName);
			connectionPoolConfig.setPassword(dbPassword);
			connectionPoolConfig.setMinConnectionsPerPartition(1);
			connectionPoolConfig.setMaxConnectionsPerPartition(8);
			connectionPoolConfig.setPartitionCount(3);
			connectionPoolConfig.setReleaseHelperThreads(0);
			
			// create the connection pool
			connectionPool = new BoneCP(connectionPoolConfig);
			
			// verify all the tables in the DB exist (create if not)
			verifyTables();
						
		} catch (SQLException e) {
			// connection problem
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// driver not found!
			e.printStackTrace();
		}
	}

	
	
	/*
	 *  validates that all the tables exist in the DB,
	 *  and if a table doesn't exist - create it.
	 */
	private void verifyTables() {
		ResultSet res = null;
		Connection connection = null;
		try{
			connection = connectionPool.getConnection();
			
			// create a list of tables the DB contains
			DatabaseMetaData meta = connection.getMetaData();
			res = meta.getTables(null, null, null, new String[] { "TABLE" });
			Set<String> tables = new TreeSet<String>();
			while (res.next()) { tables.add(res.getString("TABLE_NAME")); }
	
			if (!tables.contains("users")) { // create users table
				connection.createStatement().execute("CREATE TABLE users(username text NOT NULL, password text NOT NULL, " +
						"firstname text, lastname text, email text, phone text, isadmin boolean NOT NULL, " +
						"CONSTRAINT users_pkey PRIMARY KEY (username )) WITH ( OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
				// create the first admin
				User chuckNurris = new User("chuckn", "god", "Chuck", "Norris",
						"gmail@chuck.norris", "1-800", true);
				DBConnection.addUser(chuckNurris);
			}
			
			if (!tables.contains("tasks")) {
				// create tasks table. lat and lng are also saved to save process time. 
				connection.createStatement().execute("CREATE TABLE tasks(taskid serial NOT NULL," +
						"ownername text NOT NULL,creationtime date NOT NULL,taskname text," +
						"capacity integer, content text, geotag geography NOT NULL," +
						"latnum double precision NOT NULL,longnum double precision NOT NULL," +
						"skills integer[]  NOT NULL, " +
						"CONSTRAINT tasks_pkey PRIMARY KEY (taskid )," + 
						"CONSTRAINT tasks_ownername_fkey FOREIGN KEY (ownername) " +
						"REFERENCES users (username) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
			}
			
			if (!tables.contains("teams")) {
				// create teams table. lat and lng are also saved to save proccess time.
				connection.createStatement().execute("CREATE TABLE teams(teamid serial NOT NULL," +
						"ownername text NOT NULL, creationtime date NOT NULL, teamname text," +
						"capacity integer, content text, geotag geography NOT NULL," +
						"latnum double precision NOT NULL, longnum double precision NOT NULL, " +
						"skills integer[] NOT NULL, team_limit integer NOT NULL, CONSTRAINT teams_pkey PRIMARY KEY (teamid )," + 
						"CONSTRAINT teams_ownername_fkey FOREIGN KEY (ownername) " +
						"REFERENCES users (username) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
			}
			
			if (!tables.contains("user_in_team")) {
				// create teams table
				connection.createStatement().execute("CREATE TABLE user_in_team(teamid integer NOT NULL," +
						"username text NOT NULL, CONSTRAINT user_in_team_pkey PRIMARY KEY (teamid, username )," + 
						"CONSTRAINT user_in_team_ownername_fkey FOREIGN KEY (username) " +
						"REFERENCES users (username) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, " +
						"CONSTRAINT user_in_team_teamid_fkey FOREIGN KEY (teamid) " +
						"REFERENCES teams (teamid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
			}
			
			if (!tables.contains("skills")) {
				// create teams table
				connection.createStatement().execute("CREATE TABLE skills(sid serial NOT NULL," +
						"skillname text UNIQUE NOT NULL, available boolean NOT NULL, " +
						"CONSTRAINT skills_pkey PRIMARY KEY (sid )) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
			}
			
			if (!tables.contains("matches")) {
				// create teams table
				connection.createStatement().execute("CREATE TABLE matches(teamid integer NOT NULL," +
						"taskid integer NOT NULL, CONSTRAINT matches_pkey PRIMARY KEY (teamid, taskid )," + 
						"CONSTRAINT matches_teamid_fkey FOREIGN KEY (teamid) " +
						"REFERENCES teams (teamid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, " +
						"CONSTRAINT matches_taskid_fkey FOREIGN KEY (taskid) " +
						"REFERENCES tasks (taskid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE) " +
						"WITH (OIDS=FALSE)");
				connection.createStatement().execute("ALTER TABLE users OWNER TO " + dbUserName);
			}

		} catch (SQLException e) { e.printStackTrace(); } catch (RescueMeException ignore) {} 
		finally {
			try { res.close(); }
			catch (SQLException e) { e.printStackTrace(); }
		}
	}
	
	
	/**
	 * Returns a connection from the connection pool
	 * @return Connection
	 * @throws SQLException - on error
	 */
	public static Connection getConnection() throws SQLException{
		return connectionPool.getConnection();
	}
	
	public static final String welcomePage = "/welcome.jsp";
}
